﻿using CPPEI.ETL.Infrastructure;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CPPEI.ETL.Domain.Util
{
    /// <summary>
    /// 对数据表进行操作的类
    /// </summary>
    static class DtTableUtil
    {
        public static void DeleteRawDataTable(EtlDbContext dbContext,
            string tableName,
            string cacheTableName)
        {
            if(!string.IsNullOrEmpty(tableName))
            {
                string sql = SQLTool.GetDropTableSQL(tableName);
                try
                {
                    dbContext.Database.ExecuteSqlCommand(sql);
                }
                catch (Exception ex)
                {
                    Logger.ErrorToTag(Library.LogTAG, string.Format("删除数据表SQL:{0},出错,ex:{1}", sql, ex));
                }
            }

            if (!string.IsNullOrEmpty(cacheTableName))
            {
                string sqlCache = SQLTool.GetDropTableSQL(cacheTableName);
                try
                {
                    dbContext.Database.ExecuteSqlCommand(sqlCache);
                }
                catch (Exception ex)
                {
                    Logger.ErrorToTag(Library.LogTAG, string.Format("删除缓存数据表SQL:{0},出错,ex:{1}", sqlCache, ex));
                }
            }
        }


        /// <summary>
        /// 创建数据表，并创建数据缓存表
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="tableName"></param>
        /// <param name="cacheTableName"></param>
        /// <param name="isRawDt"></param>
        /// <param name="names"></param>
        /// <param name="types"></param>
        /// <param name="lengths"></param>
        /// <param name="nullables"></param>
        /// <returns></returns>
        public static bool CreateRawDataTable(EtlDbContext dbContext,
            string tableName,
            string cacheTableName,
            bool isRawDt,
            List<string> names,
            List<string> types,
            List<string> lengths,
            List<bool> nullables)
        {
            #region 创建数据表

            #region sys fields
            List<string> pks = new List<string>();
            pks.Add(DEF.DtTableColumns.SYSCOL_Id);

            #endregion

            List<string> rawNames = new List<string>(names);
            List<string> rawTypes = new List<string>(types);
            List<string> rawLengths = new List<string>(lengths);
            List<bool> rawNullables = new List<bool>(nullables);

            rawNames.Add(DEF.DtTableColumns.SYSCOL_Id);
            rawTypes.Add("uniqueidentifier");
            rawLengths.Add(string.Empty);
            rawNullables.Add(false);

            rawNames.Add(DEF.DtTableColumns.SYSCOL_BatchId);
            rawTypes.Add("uniqueidentifier");
            rawLengths.Add(string.Empty);
            rawNullables.Add(false);

            rawNames.Add(DEF.DtTableColumns.SYSCOL_CreatedTime);
            rawTypes.Add("datetime");
            rawLengths.Add(string.Empty);
            rawNullables.Add(false);

            rawNames.Add(DEF.DtTableColumns.SYSCOL_RowIndex);
            rawTypes.Add("int");
            rawLengths.Add(string.Empty);
            rawNullables.Add(false);

            string sqlDT = SQLTool.GetCreateTableSQL(tableName, pks, rawNames, rawTypes, rawLengths, rawNullables);

            Logger.DebugToTag(Library.LogTAG, string.Format("创建表SQL:{0}", sqlDT));

            try
            {
                dbContext.Database.ExecuteSqlCommand(sqlDT);
            }
            catch (Exception ex)
            {
                Logger.ErrorToTag(Library.LogTAG, string.Format("创建表SQL:{0},出错,ex:{1}", sqlDT, ex));
            }
            #endregion

            #region 创建缓存表

            List<string> cacheNames = new List<string>(names);
            List<string> cacheTypes = new List<string>();
            List<string> cacheLengths = new List<string>();
            List<bool> cacheNullables = new List<bool>();
            for (int index = 0; index < names.Count; index++)
            {
                cacheTypes.Add("nvarchar");
                cacheLengths.Add("512");
                cacheNullables.Add(true);
            }

            cacheNames.Add(DEF.DtTableColumns.SYSCOL_Id);
            cacheTypes.Add("uniqueidentifier");
            cacheLengths.Add(string.Empty);
            cacheNullables.Add(false);

            cacheNames.Add(DEF.DtTableColumns.SYSCOL_BatchId);
            cacheTypes.Add("uniqueidentifier");
            cacheLengths.Add(string.Empty);
            cacheNullables.Add(false);

            cacheNames.Add(DEF.DtTableColumns.SYSCOL_CreatedTime);
            cacheTypes.Add("datetime");
            cacheLengths.Add(string.Empty);
            cacheNullables.Add(false);

            cacheNames.Add(DEF.DtTableColumns.SYSCOL_RowIndex);
            cacheTypes.Add("int");
            cacheLengths.Add(string.Empty);
            cacheNullables.Add(false);

            cacheNames.Add(DEF.DtTableColumns.SYSCOL_State);
            cacheTypes.Add("int");
            cacheLengths.Add(string.Empty);
            cacheNullables.Add(false);


            string sqlCache = SQLTool.GetCreateTableSQL(cacheTableName, pks, cacheNames, cacheTypes, cacheLengths, cacheNullables);

            Logger.DebugToTag(Library.LogTAG, string.Format("创建表SQL:{0}", sqlCache));

            try
            {
                dbContext.Database.ExecuteSqlCommand(sqlCache);
                return true;
            }
            catch (Exception ex)
            {
                Logger.ErrorToTag(Library.LogTAG, string.Format("创建表SQL:{0},出错,ex:{1}", sqlCache, ex));
                return false;
            }

            #endregion
        }


        public static bool CreateCacheDataTable(EtlDbContext dbContext,
            string cacheTableName,
            List<string> names,
            List<string> types,
            List<string> lengths,
            List<bool> nullables)
        {
            List<string> pks = new List<string>();
            pks.Add(DEF.DtTableColumns.SYSCOL_Id);

            #region 创建缓存表

            List<string> cacheNames = new List<string>(names);
            List<string> cacheTypes = new List<string>();
            List<string> cacheLengths = new List<string>();
            List<bool> cacheNullables = new List<bool>();
            for (int index = 0; index < names.Count; index++)
            {
                cacheTypes.Add("nvarchar");
                cacheLengths.Add("512");
                cacheNullables.Add(true);
            }

            cacheNames.Add(DEF.DtTableColumns.SYSCOL_Id);
            cacheTypes.Add("uniqueidentifier");
            cacheLengths.Add(string.Empty);
            cacheNullables.Add(false);

            cacheNames.Add(DEF.DtTableColumns.SYSCOL_BatchId);
            cacheTypes.Add("uniqueidentifier");
            cacheLengths.Add(string.Empty);
            cacheNullables.Add(false);

            cacheNames.Add(DEF.DtTableColumns.SYSCOL_CreatedTime);
            cacheTypes.Add("datetime");
            cacheLengths.Add(string.Empty);
            cacheNullables.Add(false);

            cacheNames.Add(DEF.DtTableColumns.SYSCOL_RowIndex);
            cacheTypes.Add("int");
            cacheLengths.Add(string.Empty);
            cacheNullables.Add(false);

            cacheNames.Add(DEF.DtTableColumns.SYSCOL_State);
            cacheTypes.Add("int");
            cacheLengths.Add(string.Empty);
            cacheNullables.Add(false);


            string sqlCache = SQLTool.GetCreateTableSQL(cacheTableName, pks, cacheNames, cacheTypes, cacheLengths, cacheNullables);

            Logger.DebugToTag(Library.LogTAG, string.Format("创建表SQL:{0}", sqlCache));

            try
            {
                dbContext.Database.ExecuteSqlCommand(sqlCache);
                return true;
            }
            catch (Exception ex)
            {
                Logger.ErrorToTag(Library.LogTAG, string.Format("创建表SQL:{0},出错,ex:{1}", sqlCache, ex));
                return false;
            }

            #endregion
        }

        /// <summary>
        /// 插入数据到数据表
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="tableName"></param>
        /// <param name="id"></param>
        /// <param name="batchId"></param>
        /// <param name="createdTime"></param>
        /// <param name="rowIndex"></param>
        /// <param name="columns"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static bool InsertToRawTable(EtlDbContext dbContext,
            string tableName,
            Guid id,
            Guid batchId,
            DateTime createdTime,
            int rowIndex,
            List<string> columns,
            List<object> values)
        {
            List<string> tmpColumns = new List<string>(columns);
            List<object> tmpValues = new List<object>(values);

            tmpColumns.Add(DEF.DtTableColumns.SYSCOL_Id);
            tmpColumns.Add(DEF.DtTableColumns.SYSCOL_BatchId);
            tmpColumns.Add(DEF.DtTableColumns.SYSCOL_CreatedTime);
            tmpColumns.Add(DEF.DtTableColumns.SYSCOL_RowIndex);

            string sql = CPPEI.ETL.Infrastructure.SQLTool.GetInsertSQL(tableName, tmpColumns);

            tmpValues.Add(id);
            tmpValues.Add(batchId);
            tmpValues.Add(createdTime);
            tmpValues.Add(rowIndex);

            try
            {
                dbContext.Database.ExecuteSqlCommand(sql, tmpValues.ToArray());
                return true;
            }
            catch (Exception ex)
            {
                Logger.ErrorToTag(Library.LogTAG, string.Format("向表:{0}插入数据SQL:{1},出错,ex:{2}", tableName, sql, ex));
                return false;
            }
        }

        /// <summary>
        /// 插入到数据表
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="tableName"></param>
        /// <param name="id"></param>
        /// <param name="batchId"></param>
        /// <param name="createdTime"></param>
        /// <param name="rowIndex"></param>
        /// <param name="columns"></param>
        /// <param name="values"></param>
        /// <param name="uniqueColumns"></param>
        /// <returns></returns>
        public static bool InsertToTable(EtlDbContext dbContext,
            string tableName,
            Guid id,
            Guid batchId,
            DateTime createdTime,
            int rowIndex,
            List<string> columns,
            List<object> values,
            List<string> uniqueColumns)
        {
            List<object> uniqueValues = new List<object>();

            StringBuilder selectPartBuilder = new StringBuilder();
            StringBuilder wherePartBuilder = new StringBuilder();
            for (int uniqueIndex = 0; uniqueIndex < uniqueColumns.Count; uniqueIndex++)
            {
                for (int colIndex = 0; colIndex < columns.Count; colIndex++)
                {
                    if (string.Equals(columns[colIndex], uniqueColumns[uniqueIndex]))
                    {
                        uniqueValues.Add(values[colIndex]);
                        if (selectPartBuilder.Length == 0)
                        {
                            selectPartBuilder.AppendFormat("[{0}]", columns[colIndex]);
                        }
                        else
                        {
                            selectPartBuilder.AppendFormat(",[{0}]", columns[colIndex]);
                        }

                        if (wherePartBuilder.Length == 0)
                        {
                            wherePartBuilder.AppendFormat("[{0}]=@{0}", columns[colIndex]);
                        }
                        else
                        {
                            wherePartBuilder.AppendFormat(" and [{0}]=@{0}", columns[colIndex]);
                        }
                        break;
                    }
                }
            }

            string selectSQL = CPPEI.ETL.Infrastructure.SQLTool.GetSelectSQL(tableName, selectPartBuilder.ToString(), wherePartBuilder.ToString());

            SqlConnection sqlConnection = dbContext.Database.Connection as SqlConnection;
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = selectSQL;
            sqlCommand.Connection = sqlConnection;
            for (int uniqueColIndex = 0; uniqueColIndex < uniqueColumns.Count; uniqueColIndex++)
            {
                sqlCommand.Parameters.Add(new SqlParameter(string.Format("@{0}", uniqueColumns[uniqueColIndex]), uniqueValues[uniqueColIndex]));
            }
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCommand);

            DataTable dataTable = new DataTable();
            sqlAdapter.Fill(dataTable);
            if (dataTable.Rows.Count > 0)
            {
                #region 更新数据
                // 更新数据
                List<string> updateConditions = new List<string>();
                for (int uniqueIndex = 0; uniqueIndex < uniqueColumns.Count; uniqueIndex++)
                {
                    updateConditions.Add(uniqueColumns[uniqueIndex]);
                }

                List<string> tmpColumns = new List<string>(columns);
                List<object> tmpValues = new List<object>(values);

                string updateSQL = CPPEI.ETL.Infrastructure.SQLTool.GetUpdateSQL_V2(tableName, tmpColumns, updateConditions);

                SqlCommand sqlUpdateCommand = new SqlCommand();
                sqlUpdateCommand.CommandType = CommandType.Text;
                sqlUpdateCommand.CommandText = updateSQL;
                sqlUpdateCommand.Connection = sqlConnection;

                for (int colIndex = 0; colIndex < tmpColumns.Count; colIndex++)
                {
                    SqlParameter sqlParameter = new SqlParameter(string.Format("@{0}", tmpColumns[colIndex]), tmpValues[colIndex]);
                    sqlUpdateCommand.Parameters.Add(sqlParameter);
                }

                try
                {
                    if (sqlConnection.State != ConnectionState.Open)
                    {
                        sqlConnection.Open();
                    }
                    sqlUpdateCommand.ExecuteNonQuery();
                    return true;
                }
                catch (Exception ex)
                {
                    Logger.ErrorToTag(Library.LogTAG, string.Format("向表:{0}更新数据SQL:{1}, 出错,ex:{2}", tableName, updateSQL, ex));
                    return false;
                }
                #endregion
            }
            else
            {
                List<string> tmpColumns = new List<string>(columns);
                List<object> tmpValues = new List<object>(values);

                string sql = CPPEI.ETL.Infrastructure.SQLTool.GetInsertSQL(tableName, tmpColumns);


                try
                {
                    dbContext.Database.ExecuteSqlCommand(sql, tmpValues.ToArray());
                    return true;
                }
                catch (Exception ex)
                {
                    Logger.ErrorToTag(Library.LogTAG, string.Format("向表:{0}插入数据SQL:{1},出错,ex:{2}", tableName, sql, ex));
                    return false;
                }
            }
        }

        public static bool InsertToRawTable(EtlDbContext dbContext,
            string tableName,
            Guid id,
            Guid batchId,
            DateTime createdTime,
            int rowIndex,
            List<string> columns,
            List<object> values,
            List<string> uniqueColumns)
        {
            List<object> uniqueValues = new List<object>();
            for (int uniqueIndex = 0; uniqueIndex < uniqueColumns.Count; uniqueIndex++)
            {
                for (int colIndex = 0; colIndex < columns.Count; colIndex++)
                {
                    if(string.Equals(columns[colIndex], uniqueColumns[uniqueIndex]))
                    {
                        uniqueValues.Add(values[colIndex]);
                        break;
                    }
                }
            }

            string selectSQL = CPPEI.ETL.Infrastructure.SQLTool.GetSelectPKSQL_V2(tableName, uniqueColumns, DEF.DtTableColumns.SYSCOL_Id);

            SqlConnection sqlConnection = dbContext.Database.Connection as SqlConnection;
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = selectSQL;
            sqlCommand.Connection = sqlConnection;
            for (int uniqueColIndex = 0; uniqueColIndex < uniqueColumns.Count; uniqueColIndex++)
            {
                sqlCommand.Parameters.Add(new SqlParameter(string.Format("@{0}", uniqueColumns[uniqueColIndex]), uniqueValues[uniqueColIndex]));
            }
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCommand);

            DataTable dataTable = new DataTable();
            sqlAdapter.Fill(dataTable);
            if(dataTable.Rows.Count>0)
            {
                #region 更新数据
                string pkValue = Convert.ToString(dataTable.Rows[0]["PK"]);
                // 更新数据
                List<string> updateConditions = new List<string>();
                updateConditions.Add(DEF.DtTableColumns.SYSCOL_Id);

                List<string> tmpColumns = new List<string>(columns);
                List<object> tmpValues = new List<object>(values);

                tmpColumns.Add(DEF.DtTableColumns.SYSCOL_BatchId);
                tmpColumns.Add(DEF.DtTableColumns.SYSCOL_CreatedTime);
                tmpColumns.Add(DEF.DtTableColumns.SYSCOL_RowIndex);
                tmpValues.Add(batchId);
                tmpValues.Add(createdTime);
                tmpValues.Add(rowIndex);

                string updateSQL = CPPEI.ETL.Infrastructure.SQLTool.GetUpdateSQL_V2(tableName, tmpColumns, updateConditions);

                SqlCommand sqlUpdateCommand = new SqlCommand();
                sqlUpdateCommand.CommandType = CommandType.Text;
                sqlUpdateCommand.CommandText = updateSQL;
                sqlUpdateCommand.Connection = sqlConnection;

                for (int colIndex = 0; colIndex < tmpColumns.Count; colIndex++)
                {
                    //Logger.InfoToTag(Library.LogTAG, string.Format("向表:{0}更新数据设置参数:{0}:{1}", tableName, tmpColumns[colIndex], tmpValues[colIndex]));
                    if(tmpValues[colIndex] == null || string.IsNullOrEmpty(tmpValues[colIndex].ToString()))
                    {
                        SqlParameter sqlParameter = new SqlParameter(string.Format("@{0}", tmpColumns[colIndex]), DBNull.Value);
                        sqlUpdateCommand.Parameters.Add(sqlParameter);
                    }
                    else
                    {
                        SqlParameter sqlParameter = new SqlParameter(string.Format("@{0}", tmpColumns[colIndex]), tmpValues[colIndex]);
                        sqlUpdateCommand.Parameters.Add(sqlParameter);
                    }
                }

                //pk condition
                sqlUpdateCommand.Parameters.Add(new SqlParameter(string.Format("@{0}", DEF.DtTableColumns.SYSCOL_Id), pkValue));
                
                try
                {
                    if(sqlConnection.State != ConnectionState.Open)
                    {
                        sqlConnection.Open();
                    }
                    sqlUpdateCommand.ExecuteNonQuery();

                    //Logger.InfoToTag(Library.LogTAG, string.Format("向表:{0}更新数据SQL:{1}, pk:{2}", tableName, updateSQL, pkValue));
                    return true;
                }
                catch (Exception ex)
                {
                    Logger.ErrorToTag(Library.LogTAG, string.Format("向表:{0}更新数据SQL:{1}, pk:{2},出错,ex:{3}", tableName, updateSQL, pkValue, ex));
                    return false;
                }
                #endregion
            }
            else
            {
                List<string> tmpColumns = new List<string>(columns);
                List<object> tmpValues = new List<object>(values);

                tmpColumns.Add(DEF.DtTableColumns.SYSCOL_Id);
                tmpColumns.Add(DEF.DtTableColumns.SYSCOL_BatchId);
                tmpColumns.Add(DEF.DtTableColumns.SYSCOL_CreatedTime);
                tmpColumns.Add(DEF.DtTableColumns.SYSCOL_RowIndex);
                tmpValues.Add(id);
                tmpValues.Add(batchId);
                tmpValues.Add(createdTime);
                tmpValues.Add(rowIndex);

                string sql = CPPEI.ETL.Infrastructure.SQLTool.GetInsertSQL(tableName, tmpColumns);


                try
                {
                    dbContext.Database.ExecuteSqlCommand(sql, tmpValues.ToArray());
                    return true;
                }
                catch (Exception ex)
                {
                    Logger.ErrorToTag(Library.LogTAG, string.Format("向表:{0}插入数据SQL:{1},出错,ex:{2}", tableName, sql, ex));
                    return false;
                }
            }
        }

        public static bool InsertToRawTable(SqlConnection connection,
            string tableName,
            Guid id,
            Guid batchId,
            DateTime createdTime,
            int rowIndex,
            List<string> columns,
            List<object> values,
            List<string> uniqueColumns)
        {
            List<object> uniqueValues = new List<object>();
            for (int uniqueIndex = 0; uniqueIndex < uniqueColumns.Count; uniqueIndex++)
            {
                for (int colIndex = 0; colIndex < columns.Count; colIndex++)
                {
                    if (string.Equals(columns[colIndex], uniqueColumns[uniqueIndex]))
                    {
                        uniqueValues.Add(values[colIndex]);
                        break;
                    }
                }
            }

            string selectSQL = CPPEI.ETL.Infrastructure.SQLTool.GetSelectPKSQL_V2(tableName, uniqueColumns, DEF.DtTableColumns.SYSCOL_Id);

            SqlConnection sqlConnection = connection;
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = selectSQL;
            sqlCommand.Connection = sqlConnection;
            for (int uniqueColIndex = 0; uniqueColIndex < uniqueColumns.Count; uniqueColIndex++)
            {
                sqlCommand.Parameters.Add(new SqlParameter(string.Format("@{0}", uniqueColumns[uniqueColIndex]), uniqueValues[uniqueColIndex]));
            }
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCommand);

            DataTable dataTable = new DataTable();
            sqlAdapter.Fill(dataTable);
            if (dataTable.Rows.Count > 0)
            {
                #region 更新数据
                string pkValue = Convert.ToString(dataTable.Rows[0]["PK"]);
                // 更新数据
                List<string> updateConditions = new List<string>();
                updateConditions.Add(DEF.DtTableColumns.SYSCOL_Id);

                List<string> tmpColumns = new List<string>(columns);
                List<object> tmpValues = new List<object>(values);

                tmpColumns.Add(DEF.DtTableColumns.SYSCOL_BatchId);
                tmpColumns.Add(DEF.DtTableColumns.SYSCOL_CreatedTime);
                tmpColumns.Add(DEF.DtTableColumns.SYSCOL_RowIndex);
                tmpValues.Add(batchId);
                tmpValues.Add(createdTime);
                tmpValues.Add(rowIndex);

                string updateSQL = CPPEI.ETL.Infrastructure.SQLTool.GetUpdateSQL_V2(tableName, tmpColumns, updateConditions);

                SqlCommand sqlUpdateCommand = new SqlCommand();
                sqlUpdateCommand.CommandType = CommandType.Text;
                sqlUpdateCommand.CommandText = updateSQL;
                sqlUpdateCommand.Connection = sqlConnection;

                for (int colIndex = 0; colIndex < tmpColumns.Count; colIndex++)
                {
                    //Logger.InfoToTag(Library.LogTAG, string.Format("向表:{0}更新数据设置参数:{0}:{1}", tableName, tmpColumns[colIndex], tmpValues[colIndex]));
                    if (tmpValues[colIndex] == null || string.IsNullOrEmpty(tmpValues[colIndex].ToString()))
                    {
                        SqlParameter sqlParameter = new SqlParameter(string.Format("@{0}", tmpColumns[colIndex]), DBNull.Value);
                        sqlUpdateCommand.Parameters.Add(sqlParameter);
                    }
                    else
                    {
                        SqlParameter sqlParameter = new SqlParameter(string.Format("@{0}", tmpColumns[colIndex]), tmpValues[colIndex]);
                        sqlUpdateCommand.Parameters.Add(sqlParameter);
                    }
                }

                //pk condition
                sqlUpdateCommand.Parameters.Add(new SqlParameter(string.Format("@{0}", DEF.DtTableColumns.SYSCOL_Id), pkValue));

                try
                {
                    if (sqlConnection.State != ConnectionState.Open)
                    {
                        sqlConnection.Open();
                    }
                    sqlUpdateCommand.ExecuteNonQuery();

                    //Logger.InfoToTag(Library.LogTAG, string.Format("向表:{0}更新数据SQL:{1}, pk:{2}", tableName, updateSQL, pkValue));
                    return true;
                }
                catch (Exception ex)
                {
                    Logger.ErrorToTag(Library.LogTAG, string.Format("向表:{0}更新数据SQL:{1}, pk:{2},出错,ex:{3}", tableName, updateSQL, pkValue, ex));
                    return false;
                }
                #endregion
            }
            else
            {
                List<string> tmpColumns = new List<string>(columns);
                List<object> tmpValues = new List<object>(values);

                tmpColumns.Add(DEF.DtTableColumns.SYSCOL_Id);
                tmpColumns.Add(DEF.DtTableColumns.SYSCOL_BatchId);
                tmpColumns.Add(DEF.DtTableColumns.SYSCOL_CreatedTime);
                tmpColumns.Add(DEF.DtTableColumns.SYSCOL_RowIndex);
                tmpValues.Add(id);
                tmpValues.Add(batchId);
                tmpValues.Add(createdTime);
                tmpValues.Add(rowIndex);

                string sql = CPPEI.ETL.Infrastructure.SQLTool.GetInsertSQLAtName(tableName, tmpColumns);


                SqlCommand insertCmd = new SqlCommand(sql, connection);
                for (int index = 0; index < tmpValues.Count; index++)
                {
                    insertCmd.Parameters.Add(new SqlParameter(string.Format("@{0}", tmpColumns[index]), tmpValues[index]));
                }
                try
                {
                    insertCmd.ExecuteNonQuery();
                    return true;
                }
                catch (Exception ex)
                {
                    Logger.ErrorToTag(Library.LogTAG, string.Format("向表:{0}插入数据SQL:{1},出错,ex:{2}", tableName, sql, ex));
                    return false;
                }
            }
        }
        /// <summary>
        /// 
        /// 更新数据表的某一个值
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="tableName"></param>
        /// <param name="dataId"></param>
        /// <param name="column"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static bool UpdateDataValueToTable(EtlDbContext dbContext,
            string tableName,
            Guid dataId,
            string column,
            string value)
        {
            List<string> columns = new List<string>();
            columns.Add(column);

            List<string> conditions = new List<string>();
            conditions.Add(DEF.DtTableColumns.SYSCOL_Id);

            string sql = CPPEI.ETL.Infrastructure.SQLTool.GetUpdateSQLV1(tableName, columns, conditions);

            List<object> values = new List<object>();
            values.Add(value);
            values.Add(dataId);

            try
            {
                dbContext.Database.ExecuteSqlCommand(sql, values.ToArray());
                return true;
            }
            catch (Exception ex)
            {
                Logger.ErrorToTag(Library.LogTAG, string.Format("向表:{0}更新数据SQL:{1},出错,ex:{2}", tableName, sql, ex));
                return false;
            }
        }

        public static bool UpdateDataValueToTable(EtlDbContext dbContext,
            string tableName,
            string[] pkColumns,
            string[] pkValues,
            string column,
            string value,
            out string oldValue)
        {
            oldValue = null;

            List<string> columns = new List<string>();
            columns.Add(column);

            List<string> conditions = new List<string>(pkColumns);

            string selectSQL = CPPEI.ETL.Infrastructure.SQLTool.GetSelectSQL_V2(tableName, column, conditions);
            
            SqlConnection sqlConnection = dbContext.Database.Connection as SqlConnection;
            SqlCommand sqlSelectCommand = new SqlCommand();
            sqlSelectCommand.CommandType = CommandType.Text;
            sqlSelectCommand.CommandText = selectSQL;
            sqlSelectCommand.Connection = sqlConnection;

            for (int index = 0; index < pkColumns.Length; index++)
            {
                sqlSelectCommand.Parameters.Add(new SqlParameter(string.Format("@{0}", pkColumns[index]), pkValues[index]));
            }

            try
            {
                if (sqlConnection.State != ConnectionState.Open)
                {
                    sqlConnection.Open();
                }

                SqlDataReader sqlReader = sqlSelectCommand.ExecuteReader();
                sqlReader.Read();
                oldValue = Convert.ToString(sqlReader[column]);
                sqlReader.Close();
            }
            catch(Exception ex)
            {
                return false;
            }

            string updateSQL = CPPEI.ETL.Infrastructure.SQLTool.GetUpdateSQL_V2(tableName, columns, conditions);

            SqlCommand sqlUpdateCommand = new SqlCommand();
            sqlUpdateCommand.CommandType = CommandType.Text;
            sqlUpdateCommand.CommandText = updateSQL;
            sqlUpdateCommand.Connection = sqlConnection;

            for (int index = 0; index < pkColumns.Length; index++)
            {
                sqlUpdateCommand.Parameters.Add(new SqlParameter(string.Format("@{0}", pkColumns[index]), pkValues[index]));
            }
            sqlUpdateCommand.Parameters.Add(new SqlParameter(string.Format("@{0}", column), value));

            try
            {
                if (sqlConnection.State != ConnectionState.Open)
                {
                    sqlConnection.Open();
                }

                sqlUpdateCommand.ExecuteNonQuery();

                return true;
            }
            catch (Exception ex)
            {
                Logger.ErrorToTag(Library.LogTAG, string.Format("向表:{0}更新数据SQL:{1},出错,ex:{2}", tableName, updateSQL, ex));
                return false;
            }
        }

        public static bool UpdateDataValueToTable(EtlDbContext dbContext,
            string tableName,
            string[] pkColumns,
            string[] pkValues,
            string[] columns,
            string[] values)
        {
            string updateSQL = CPPEI.ETL.Infrastructure.SQLTool.GetUpdateSQL_V2(tableName, new List<string>(columns), new List<string>(pkColumns));
           SqlConnection sqlConnection = (SqlConnection)dbContext.Database.Connection;
            SqlCommand sqlUpdateCommand = new SqlCommand();
            sqlUpdateCommand.CommandType = CommandType.Text;
            sqlUpdateCommand.CommandText = updateSQL;
            sqlUpdateCommand.Connection = sqlConnection;

            for (int index = 0; index < pkColumns.Length; index++)
            {
                sqlUpdateCommand.Parameters.Add(new SqlParameter(string.Format("@{0}", pkColumns[index]), pkValues[index]));
            }

            for (int index = 0; index < columns.Length; index++)
            {
                sqlUpdateCommand.Parameters.Add(new SqlParameter(string.Format("@{0}", columns[index]), values[index]));
            }

            try
            {
                if (sqlConnection.State != ConnectionState.Open)
                {
                    sqlConnection.Open();
                }

                sqlUpdateCommand.ExecuteNonQuery();

                return true;
            }
            catch (Exception ex)
            {
                Logger.ErrorToTag(Library.LogTAG, string.Format("向表:{0}更新数据SQL:{1},出错,ex:{2}", tableName, updateSQL, ex));
                return false;
            }
        }

        public static bool UpdateToCacheTable(EtlDbContext dbContext,
            string tableName,
            Guid batchId,
            Guid dataId,
            DateTime createdTime,
            int state)
        {
            List<string> columns = new List<string>();
            columns.Add(DEF.DtTableColumns.SYSCOL_State);
            columns.Add(DEF.DtTableColumns.SYSCOL_CreatedTime);

            List<string> conditions = new List<string>();
            conditions.Add(DEF.DtTableColumns.SYSCOL_BatchId);
            conditions.Add(DEF.DtTableColumns.SYSCOL_Id);

            string sql = CPPEI.ETL.Infrastructure.SQLTool.GetUpdateSQLV1(tableName, columns, conditions);

            List<object> values = new List<object>();
            values.Add(state);
            values.Add(createdTime);
            values.Add(batchId);
            values.Add(dataId);

            try
            {
                dbContext.Database.ExecuteSqlCommand(sql, values.ToArray());
                return true;
            }
            catch (Exception ex)
            {
                Logger.ErrorToTag(Library.LogTAG, string.Format("向表:{0}更新数据SQL:{1},出错,ex:{2}", tableName, sql, ex));
                return false;
            }
        }

        /// <summary>
        /// 插入数据到缓存数据表
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="tableName"></param>
        /// <param name="id"></param>
        /// <param name="batchId"></param>
        /// <param name="rowIndex"></param>
        /// <param name="state"></param>
        /// <param name="createdTime"></param>
        /// <param name="columns"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static bool InsertToCacheTable(EtlDbContext dbContext,
            string tableName,
            Guid id,
            Guid batchId,
            int rowIndex,
            int state,
            DateTime createdTime,
            List<string> columns,
            List<object> values)
        {
            List<string> tmpColumns = new List<string>(columns);
            List<object> tmpValues = new List<object>(values);

            tmpColumns.Add(DEF.DtTableColumns.SYSCOL_Id);
            tmpColumns.Add(DEF.DtTableColumns.SYSCOL_BatchId);
            tmpColumns.Add(DEF.DtTableColumns.SYSCOL_CreatedTime);
            tmpColumns.Add(DEF.DtTableColumns.SYSCOL_RowIndex);
            tmpColumns.Add(DEF.DtTableColumns.SYSCOL_State);
            string sql = CPPEI.ETL.Infrastructure.SQLTool.GetInsertSQL(tableName, tmpColumns);

            tmpValues.Add(id);
            tmpValues.Add(batchId);
            tmpValues.Add(createdTime);
            tmpValues.Add(rowIndex);
            tmpValues.Add(state);

            try
            {
                dbContext.Database.ExecuteSqlCommand(sql, tmpValues.ToArray());
                return true;
            }
            catch (Exception ex)
            {
                Logger.ErrorToTag(Library.LogTAG, string.Format("向表:{0}插入数据SQL:{1},出错,ex:{2}", tableName, sql, ex));
                return false;
            }
        }

        public static DataTable GetPagedDataFromCacheTable(EtlDbContext dbContext, string tableName, Guid batchId, int state, List<string> columns,
            int start, 
            int limit,
            out int total)
        {
            // 获取id列
            columns.Add(DEF.DtTableColumns.SYSCOL_Id);

            List<string> conditions = new List<string>();
            conditions.Add(DEF.DtTableColumns.SYSCOL_BatchId);
            conditions.Add(DEF.DtTableColumns.SYSCOL_State);

            string sql = CPPEI.ETL.Infrastructure.SQLTool.GetSelectPagedSQL(tableName, columns, conditions, DEF.DtTableColumns.SYSCOL_RowIndex, start, limit);

            string sqlTotal = CPPEI.ETL.Infrastructure.SQLTool.GetSelectTotalCountSQL(tableName, conditions, DEF.DtTableColumns.SYSCOL_Id);

            SqlConnection sqlConnection = dbContext.Database.Connection as SqlConnection;
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = sql;
            sqlCommand.Connection = sqlConnection;
            sqlCommand.Parameters.Add(new SqlParameter(string.Format("@{0}", DEF.DtTableColumns.SYSCOL_BatchId), batchId));
            sqlCommand.Parameters.Add(new SqlParameter(string.Format("@{0}", DEF.DtTableColumns.SYSCOL_State), state));
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCommand);

            DataTable dataTable = new DataTable();
            sqlAdapter.Fill(dataTable);

            #region 获取总数
            SqlCommand sqlCommandTotal = new SqlCommand();
            sqlCommandTotal.CommandType = CommandType.Text;
            sqlCommandTotal.CommandText = sqlTotal;
            sqlCommandTotal.Connection = sqlConnection;
            sqlCommandTotal.Parameters.Add(new SqlParameter(string.Format("@{0}", DEF.DtTableColumns.SYSCOL_BatchId), batchId));
            sqlCommandTotal.Parameters.Add(new SqlParameter(string.Format("@{0}", DEF.DtTableColumns.SYSCOL_State), state));
            SqlDataAdapter sqlAdapterTotal = new SqlDataAdapter(sqlCommandTotal);

            DataTable dataTableTotal = new DataTable();
            sqlAdapterTotal.Fill(dataTableTotal);
            if(dataTableTotal != null)
            {
                total = Convert.ToInt32(dataTableTotal.Rows[0][0]);
            }
            else
            {
                total = 0;
            }
            #endregion

            return dataTable;
        }

        public static DataTable GetPagedDataFromCacheTable(SqlConnection sqlConnection, string tableName, Guid batchId, int state, List<string> columns,
            int start,
            int limit,
            out int total)
        {
            // 获取id列
            columns.Add(DEF.DtTableColumns.SYSCOL_Id);

            List<string> conditions = new List<string>();
            conditions.Add(DEF.DtTableColumns.SYSCOL_BatchId);
            conditions.Add(DEF.DtTableColumns.SYSCOL_State);

            string sql = CPPEI.ETL.Infrastructure.SQLTool.GetSelectPagedSQL(tableName, columns, conditions, DEF.DtTableColumns.SYSCOL_RowIndex, start, limit);

            string sqlTotal = CPPEI.ETL.Infrastructure.SQLTool.GetSelectTotalCountSQL(tableName, conditions, DEF.DtTableColumns.SYSCOL_Id);

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = sql;
            sqlCommand.Connection = sqlConnection;
            sqlCommand.Parameters.Add(new SqlParameter(string.Format("@{0}", DEF.DtTableColumns.SYSCOL_BatchId), batchId));
            sqlCommand.Parameters.Add(new SqlParameter(string.Format("@{0}", DEF.DtTableColumns.SYSCOL_State), state));
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCommand);

            DataTable dataTable = new DataTable();
            sqlAdapter.Fill(dataTable);

            #region 获取总数
            SqlCommand sqlCommandTotal = new SqlCommand();
            sqlCommandTotal.CommandType = CommandType.Text;
            sqlCommandTotal.CommandText = sqlTotal;
            sqlCommandTotal.Connection = sqlConnection;
            sqlCommandTotal.Parameters.Add(new SqlParameter(string.Format("@{0}", DEF.DtTableColumns.SYSCOL_BatchId), batchId));
            sqlCommandTotal.Parameters.Add(new SqlParameter(string.Format("@{0}", DEF.DtTableColumns.SYSCOL_State), state));
            SqlDataAdapter sqlAdapterTotal = new SqlDataAdapter(sqlCommandTotal);

            DataTable dataTableTotal = new DataTable();
            sqlAdapterTotal.Fill(dataTableTotal);
            if (dataTableTotal != null)
            {
                total = Convert.ToInt32(dataTableTotal.Rows[0][0]);
            }
            else
            {
                total = 0;
            }
            #endregion

            return dataTable;
        }
        public static DataTable GetPagedDataFromRawTable(EtlDbContext dbContext, string tableName, Guid batchId, List<string> columns,
            int start,
            int limit,
            out int total)
        {
            // 获取id列
            columns.Add(DEF.DtTableColumns.SYSCOL_Id);

            List<string> conditions = new List<string>();
            conditions.Add(DEF.DtTableColumns.SYSCOL_BatchId);

            string sql = CPPEI.ETL.Infrastructure.SQLTool.GetSelectPagedSQL(tableName, columns, conditions, DEF.DtTableColumns.SYSCOL_RowIndex, start, limit);

            string sqlTotal = CPPEI.ETL.Infrastructure.SQLTool.GetSelectTotalCountSQL(tableName, conditions, DEF.DtTableColumns.SYSCOL_Id);

            SqlConnection sqlConnection = dbContext.Database.Connection as SqlConnection;
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = sql;
            sqlCommand.Connection = sqlConnection;
            sqlCommand.Parameters.Add(new SqlParameter(string.Format("@{0}", DEF.DtTableColumns.SYSCOL_BatchId), batchId));
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCommand);

            DataTable dataTable = new DataTable();
            sqlAdapter.Fill(dataTable);

            #region 获取总数
            SqlCommand sqlCommandTotal = new SqlCommand();
            sqlCommandTotal.CommandType = CommandType.Text;
            sqlCommandTotal.CommandText = sqlTotal;
            sqlCommandTotal.Connection = sqlConnection;
            sqlCommandTotal.Parameters.Add(new SqlParameter(string.Format("@{0}", DEF.DtTableColumns.SYSCOL_BatchId), batchId));
            SqlDataAdapter sqlAdapterTotal = new SqlDataAdapter(sqlCommandTotal);

            DataTable dataTableTotal = new DataTable();
            sqlAdapterTotal.Fill(dataTableTotal);
            if (dataTableTotal != null)
            {
                total = Convert.ToInt32(dataTableTotal.Rows[0][0]);
            }
            else
            {
                total = 0;
            }
            #endregion

            return dataTable;
        }

        /// <summary>
        /// 从数据表中获取一条数据
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="tableName"></param>
        /// <param name="columns"></param>
        /// <param name="pkColumns"></param>
        /// <param name="pkValues"></param>
        /// <returns></returns>
        public static DataTable GetSingleDataFromTable(EtlDbContext dbContext, string tableName, List<string> columns, string[] pkColumns, string[] pkValues)
        {
            List<string> conditions = new List<string>(pkColumns);

            string sql = CPPEI.ETL.Infrastructure.SQLTool.GetSelectSQL(tableName, columns, conditions);

            SqlConnection sqlConnection = dbContext.Database.Connection as SqlConnection;
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = sql;
            sqlCommand.Connection = sqlConnection;

            for (int pkIndex = 0; pkIndex < pkColumns.Length; pkIndex++)
            {
                sqlCommand.Parameters.Add(new SqlParameter(string.Format("@{0}", pkColumns[pkIndex]), pkValues[pkIndex]));
            }

            SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCommand);

            DataTable dataTable = new DataTable("Table1");
            sqlAdapter.Fill(dataTable);

            return dataTable;
        }


        public static DataTable GetPagedDataFromRawTable(EtlDbContext dbContext, string tableName, List<string> columns,
            int start,
            int limit,
            out int total)
        {
            // 获取id列
            columns.Add(DEF.DtTableColumns.SYSCOL_Id);

            string sql = CPPEI.ETL.Infrastructure.SQLTool.GetSelectPagedSQL(tableName, columns, null, DEF.DtTableColumns.SYSCOL_RowIndex, start, limit);

            string sqlTotal = CPPEI.ETL.Infrastructure.SQLTool.GetSelectTotalCountSQL(tableName, null, DEF.DtTableColumns.SYSCOL_Id);

            SqlConnection sqlConnection = dbContext.Database.Connection as SqlConnection;
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = sql;
            sqlCommand.Connection = sqlConnection;
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCommand);

            DataTable dataTable = new DataTable();
            try
            {
                sqlAdapter.Fill(dataTable);
            }
            catch(Exception ex)
            {

            }

            #region 获取总数
            SqlCommand sqlCommandTotal = new SqlCommand();
            sqlCommandTotal.CommandType = CommandType.Text;
            sqlCommandTotal.CommandText = sqlTotal;
            sqlCommandTotal.Connection = sqlConnection;
            SqlDataAdapter sqlAdapterTotal = new SqlDataAdapter(sqlCommandTotal);

            try
            {
                DataTable dataTableTotal = new DataTable();
                sqlAdapterTotal.Fill(dataTableTotal);
                if (dataTableTotal != null)
                {
                    total = Convert.ToInt32(dataTableTotal.Rows[0][0]);
                }
                else
                {
                    total = 0;
                }
            }
            catch(Exception ex)
            {
                total = 0;
            }
            #endregion

            return dataTable;
        }

        /// <summary>
        /// 获取数据（非原始数据表)
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="tableName"></param>
        /// <param name="columns"></param>
        /// <param name="pks"></param>
        /// <param name="start"></param>
        /// <param name="limit"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public static DataTable GetPagedDataFromTable(EtlDbContext dbContext, string tableName, List<string> columns, List<string> pks,
            int start,
            int limit,
            out int total)
        {
            if(pks == null || pks.Count == 0)
            {
                Logger.ErrorToTag(Library.LogTAG, string.Format("GetPagedDataFromTable for \"{0}\"出错，没有主键列", tableName));
                total = 0;
                return new DataTable();
            }
            string sql = CPPEI.ETL.Infrastructure.SQLTool.GetSelectPagedSQL(tableName, columns, null, pks.First(), start, limit);

            string sqlTotal = CPPEI.ETL.Infrastructure.SQLTool.GetSelectTotalCountSQL(tableName, null, pks.First());

            SqlConnection sqlConnection = dbContext.Database.Connection as SqlConnection;
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = sql;
            sqlCommand.Connection = sqlConnection;
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCommand);

            DataTable dataTable = new DataTable();
            try
            {
                sqlAdapter.Fill(dataTable);
            }
            catch (Exception ex)
            {

            }

            #region 获取总数
            SqlCommand sqlCommandTotal = new SqlCommand();
            sqlCommandTotal.CommandType = CommandType.Text;
            sqlCommandTotal.CommandText = sqlTotal;
            sqlCommandTotal.Connection = sqlConnection;
            SqlDataAdapter sqlAdapterTotal = new SqlDataAdapter(sqlCommandTotal);

            try
            {
                DataTable dataTableTotal = new DataTable();
                sqlAdapterTotal.Fill(dataTableTotal);
                if (dataTableTotal != null)
                {
                    total = Convert.ToInt32(dataTableTotal.Rows[0][0]);
                }
                else
                {
                    total = 0;
                }
            }
            catch (Exception ex)
            {
                total = 0;
            }
            #endregion

            return dataTable;
        }

        public static DataTable GetPagedDataFromTableWithWhere(EtlDbContext dbContext, string tableName, List<string> columns, string sqlWhere,
            int start,
            int limit,
            out int total)
        {
            // 获取id列
            columns.Add(DEF.DtTableColumns.SYSCOL_Id);

            string sql = CPPEI.ETL.Infrastructure.SQLTool.GetSelectPagedSQLWithWhere(tableName, columns, sqlWhere, DEF.DtTableColumns.SYSCOL_RowIndex, start, limit);

            string sqlTotal = CPPEI.ETL.Infrastructure.SQLTool.GetSelectTotalCountSQLWithWhere(tableName, sqlWhere, DEF.DtTableColumns.SYSCOL_Id);

            SqlConnection sqlConnection = dbContext.Database.Connection as SqlConnection;
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = sql;
            sqlCommand.Connection = sqlConnection;
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCommand);

            DataTable dataTable = new DataTable();
            sqlAdapter.Fill(dataTable);

            #region 获取总数
            SqlCommand sqlCommandTotal = new SqlCommand();
            sqlCommandTotal.CommandType = CommandType.Text;
            sqlCommandTotal.CommandText = sqlTotal;
            sqlCommandTotal.Connection = sqlConnection;
            SqlDataAdapter sqlAdapterTotal = new SqlDataAdapter(sqlCommandTotal);

            DataTable dataTableTotal = new DataTable();
            sqlAdapterTotal.Fill(dataTableTotal);
            if (dataTableTotal != null)
            {
                total = Convert.ToInt32(dataTableTotal.Rows[0][0]);
            }
            else
            {
                total = 0;
            }
            #endregion

            return dataTable;
        }

        /// <summary>
        /// 从数据表获取数据
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="tableName"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public static DataTable GetDataFromTable(EtlDbContext dbContext, Guid appId, Guid tableId, string tableName, List<string> columns)
        {
            // 获取id列
            columns.Add(DEF.DtTableColumns.SYSCOL_Id);

            string sql = CPPEI.ETL.Infrastructure.SQLTool.GetSelectSQL(tableName, columns, 
                string.Format(" {0} not in (select dataid from ETL_AppExtractReceiveRecord  where appid='{1}' and datasourceid='{2}' )", 
                DEF.DtTableColumns.SYSCOL_Id, appId, tableId));

            SqlConnection sqlConnection = dbContext.Database.Connection as SqlConnection;
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = sql;
            sqlCommand.Connection = sqlConnection;
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCommand);

            DataTable dataTable = new DataTable("DataTable");
            try
            {
                sqlAdapter.Fill(dataTable);
            }
            catch (Exception ex)
            {
                Logger.ErrorToTag(Library.LogTAG, string.Format("exec sql:{0}, error, ex:{1}", sql, ex));
            }

            return dataTable;
        }

        public static DataTable GetDataFromTableWithWhere(EtlDbContext dbContext, string tableName, List<string> columns, string sqlWhere)
        {
            // 获取id列
            columns.Add(DEF.DtTableColumns.SYSCOL_Id);

            string sql = CPPEI.ETL.Infrastructure.SQLTool.GetSelectSQLWithWhere(tableName, columns, sqlWhere);

            SqlConnection sqlConnection = dbContext.Database.Connection as SqlConnection;
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = sql;
            sqlCommand.Connection = sqlConnection;
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCommand);

            DataTable dataTable = new DataTable();
            sqlAdapter.Fill(dataTable);

            return dataTable;
        }


        public static DataTable GetFromCacheTable(EtlDbContext dbContext,
            string tableName,
            Guid batchId,
            int state,
            List<string> columns)
        {
            List<string> conditions = new List<string>();
            List<object> tmpValues = new List<object>();

            conditions.Add(DEF.DtTableColumns.SYSCOL_BatchId);
            conditions.Add(DEF.DtTableColumns.SYSCOL_State);
            string sql = CPPEI.ETL.Infrastructure.SQLTool.GetSelectSQL(tableName, columns, conditions);

            tmpValues.Add(batchId);
            tmpValues.Add(state);

            try
            {
                SqlConnection sqlConnection = dbContext.Database.Connection as SqlConnection;
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.CommandType = CommandType.Text;
                sqlCommand.CommandText = sql;
                sqlCommand.Connection = sqlConnection;
                sqlCommand.Parameters.Add(new SqlParameter(string.Format("@{0}", DEF.DtTableColumns.SYSCOL_BatchId), batchId));
                sqlCommand.Parameters.Add(new SqlParameter(string.Format("@{0}", DEF.DtTableColumns.SYSCOL_State), state));
                SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCommand);

                DataTable dataTable = new DataTable();
                sqlAdapter.Fill(dataTable);
                return dataTable;
            }
            catch (Exception ex)
            {
                Logger.ErrorToTag(Library.LogTAG, string.Format("从表:{0}获取数据SQL:{1},出错,ex:{2}", tableName, sql, ex));
                return null;
            }
        }

        /// <summary>
        /// 获取外部程序抽取数据的时候被外部程序退回的数据
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="tableName"></param>
        /// <param name="columns"></param>
        /// <param name="sqlWhere">where条件</param>
        /// <param name="start"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        public static DataTable GetAppExtractRejectedDataFromTable(EtlDbContext dbContext, string tableName, List<string> columns, string sqlWhere, int start, int limit, out int total)
        {
            // 获取id列
            columns.Add(DEF.DtTableColumns.SYSCOL_Id);
            columns.Add(DEF.DtTableColumns.SYSCOL_BatchId);

            string sql = CPPEI.ETL.Infrastructure.SQLTool.GetSelectPagedSQLWithWhere(tableName, columns, sqlWhere, DEF.DtTableColumns.SYSCOL_RowIndex, start, limit);

            string sqlTotal = CPPEI.ETL.Infrastructure.SQLTool.GetSelectTotalCountSQLWithWhere(tableName, sqlWhere, DEF.DtTableColumns.SYSCOL_Id);

            SqlConnection sqlConnection = dbContext.Database.Connection as SqlConnection;
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = sql;
            sqlCommand.Connection = sqlConnection;
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCommand);

            DataTable dataTable = new DataTable();
            sqlAdapter.Fill(dataTable);

            #region 获取总数
            SqlCommand sqlCommandTotal = new SqlCommand();
            sqlCommandTotal.CommandType = CommandType.Text;
            sqlCommandTotal.CommandText = sqlTotal;
            sqlCommandTotal.Connection = sqlConnection;
            SqlDataAdapter sqlAdapterTotal = new SqlDataAdapter(sqlCommandTotal);

            DataTable dataTableTotal = new DataTable();
            sqlAdapterTotal.Fill(dataTableTotal);
            if (dataTableTotal != null)
            {
                total = Convert.ToInt32(dataTableTotal.Rows[0][0]);
            }
            else
            {
                total = 0;
            }
            #endregion

            return dataTable;
        }

        public static DataTable SelectFromTable(EtlDbContext dbContext, string tableName, string sqlSelect, string sqlWhere)
        {
            string sql = CPPEI.ETL.Infrastructure.SQLTool.GetSelectSQL(tableName, sqlSelect, sqlWhere);

            SqlConnection sqlConnection = dbContext.Database.Connection as SqlConnection;
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = sql;
            sqlCommand.Connection = sqlConnection;
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCommand);

            DataTable dataTable = new DataTable();
            sqlAdapter.Fill(dataTable);

            return dataTable;
        }

        private static string GetDBType(Type type)
        {
            return "int";
        }

        internal static void InsertToCacheTableNoTry(EtlDbContext dbContext,
            string tableName,
            Guid id,
            Guid batchId,
            int rowIndex,
            int state,
            DateTime createdTime,
            List<string> columns,
            List<object> values)
        {
            List<string> tmpColumns = new List<string>(columns);
            List<object> tmpValues = new List<object>(values);

            tmpColumns.Add(DEF.DtTableColumns.SYSCOL_Id);
            tmpColumns.Add(DEF.DtTableColumns.SYSCOL_BatchId);
            tmpColumns.Add(DEF.DtTableColumns.SYSCOL_CreatedTime);
            tmpColumns.Add(DEF.DtTableColumns.SYSCOL_RowIndex);
            tmpColumns.Add(DEF.DtTableColumns.SYSCOL_State);
            string sql = CPPEI.ETL.Infrastructure.SQLTool.GetInsertSQL(tableName, tmpColumns);

            tmpValues.Add(id);
            tmpValues.Add(batchId);
            tmpValues.Add(createdTime);
            tmpValues.Add(rowIndex);
            tmpValues.Add(state);

            dbContext.Database.ExecuteSqlCommand(sql, tmpValues.ToArray());
        }
    }
}
